﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Timers;
using System.Data.SqlClient;
using System.Configuration;

namespace sqlkeepalive
{
    public partial class SQLpoke : ServiceBase
    {
        public Timer Timer { get; set; }

        public int TimerInterval
        {
            get
            {
                string interval = ConfigurationManager.AppSettings["UpdateInterval"];
                if (string.IsNullOrEmpty(interval))
                    return 60*1000;

                int intervalInt = 60;
                if (int.TryParse(interval, out intervalInt))
                    return intervalInt*1000;

                return 60*1000;
            }
        } // 1 hour = 3600000

        public SQLpoke()
        {
            InitializeComponent();
            if (Timer == null)
            {
                // Initiate
                Timer = new Timer(TimerInterval);

                // Now tell the timer when the timer fires (the Elapsed event) call the TestSQL method in our code
                Timer.Elapsed += TestSQL;
            }
        }

        #region Start and Stop methods

        protected override void OnStart(string[] args)
        {
            Timer.Start();
        }

        protected override void OnStop()
        {
            Timer.Stop();
        }

        protected override void OnContinue()
        {
            base.OnContinue();
            Timer.Start();
        }

        protected override void OnPause()
        {
            base.OnPause();
            Timer.Stop();
        }

        protected override void OnShutdown()
        {
            base.OnShutdown();
            Timer.Stop();
        }

        #endregion

        private void TestSQL(object sender, ElapsedEventArgs e)
        {
            string writeLog = ConfigurationManager.AppSettings["WriteLog"];

            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sql"].ConnectionString))
            {
                try
                {
                    // Open...
                    myConnection.Open();
                    
                    // Activate if you want to do some sql testing
                    //using (SqlCommand myCommand = new SqlCommand("SELECT * FROM sys.Tables", myConnection))
                    //{
                    //    SqlDataReader reader = myCommand.ExecuteReader();
                    //    while (reader.Read())
                    //    { }
                    //}

                    // ... and close
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (writeLog.Equals("All", StringComparison.InvariantCultureIgnoreCase) || writeLog.Equals("Errors", StringComparison.InvariantCultureIgnoreCase))
                        WriteLog(string.Concat("Could not connect to SQL Server\n", ex.Message, "\n", ex.StackTrace), false);

                    return;
                }

                // If we got to this point, (open, close) = success.
                if (writeLog.Equals("All", StringComparison.InvariantCultureIgnoreCase))
                    WriteLog("Successfully connected to SQL Server", true);
            }
        }

        private void WriteLog(string message, bool result)
        {
            if (!EventLog.SourceExists(ServiceName))
                EventLog.CreateEventSource(ServiceName, "Application");

            EventLog.WriteEntry(ServiceName, message, result ? EventLogEntryType.SuccessAudit : EventLogEntryType.Error);
        }

    }
}
